﻿Public Class ReservationView

    Private Sub ReservationView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        UI.FormatDataGrid(DataGridView1)
        UI.SetPlaceHolder(txtsearch)
        loademp()
        loadreservtype()
        LoadData()
    End Sub
    Private Sub loademp()
        cboemp.DisplayMember = "EmployeeName"
        cboemp.ValueMember = "EmployeeId"
        cboemp.DataSource = cmd.ExecuteDataTable("SELECT EmployeeId,EmployeeName FROM Employee WHERE Active=1 UNION SELECT 0,'All'")
    End Sub
    Private Sub loadreservtype()
        cboretype.DisplayMember = "ReservationType"
        cboretype.ValueMember = "ReservationTypeId"
        cboretype.DataSource = cmd.ExecuteDataTable("SELECT ReservationTypeId,ReservationType FROM ReservationType WHERE Active=1 UNION SELECT 0,'All'")
    End Sub
    Public Sub LoadData()
        Dim sql = <sql> 
                        SELECT re.*,e.EmployeeName,rt.ReservationType,c.ClientName
                        FROM Reservation re 
                        INNER JOIN Employee e ON e.EmployeeId=re.EmployeeId
                        INNer JOIN Client c on c.ClientId=re.ClientId
                        INNER JOIN ReservationType rt ON  rt.ReservationTypeId=re.ReservationTypeId
                        WHERE re.Active=1 
                            AND ReservationDate BETWEEN @D1 AND @D2 
                            AND (@EmployeeId=0 OR re.EmployeeId=@EmployeeId ) 
                            AND (@ReTypeId=0 OR re.ReservationTypeId=@ReTypeId) 
                            AND UPPER(ReservationNo) LIKE UPPER(@S);
                 </sql>.Value

        cmd.Parameters("@D1") = Me.dtpstart.Value.Date
        cmd.Parameters("@D2") = Me.dtpend.Value.Date.AddDays(1).AddSeconds(-1)
        cmd.Parameters("@EmployeeId") = Me.cboemp.SelectedValue
        cmd.Parameters("@ReTypeId") = Me.cboretype.SelectedValue
        Cmd.Parameters("@S") = "%" + Me.txtSearch.Text + "%"
        Me.DataGridView1.DataSource = cmd.ExecuteDataTable(sql)

    End Sub

    Private Sub dtpstart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpstart.ValueChanged
        LoadData()
    End Sub

    Private Sub dtpend_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpend.ValueChanged
        LoadData()
    End Sub

    Private Sub cboemp_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboemp.SelectedIndexChanged
        LoadData()
    End Sub

    Private Sub cboretype_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboretype.SelectedIndexChanged
        LoadData()
    End Sub
End Class